package com.source.barcodeMachineGrantAndRecovery.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.source.barcodeMachineGrantAndRecovery.VO.BarcodeMachineGrantVO;
import com.source.barcodeMachineGrantAndRecovery.entity.BarcodeMachineGrant;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author Feng
 * @since 2021-02-24
 */
public interface BarcodeMachineGrantMapper extends BaseMapper<BarcodeMachineGrant> {

    String SELECT = "SELECT g.*,u1.`name` grantPersonName,u2.`name` receivePersonName,u3.`name` userName,s.stallsName\n" +
            "FROM barcode_machine_grant g, sys_user u1, sys_user u2,sys_user u3, stalls s\n" +
            "WHERE g.isDelete=0 AND g.grantPersonId=u1.userId AND g.receivePersonId=u2.userId AND g.stallsId=s.stallsId AND s.userId=u3.userId \n";

    String DISTINCT = SELECT + " AND g.grantId IN (SELECT hmg.stallsGrantId FROM house_machine_grant hmg WHERE hmg.id IN (SELECT houseGrantId FROM barcode_machine) )";

    /*ID	脚标机ID	发放档口	发放时间	发放人员	回收人员	状态	押金（元）	纸质协议编号*/
    String LIKE = "\nCONCAT(IFNULL(g.grantId, ''),IFNULL(g.barcodeMachineNumber,''),IFNULL(s.stallsName,''),IFNULL(DATE(g.grantDate),''),IFNULL(u1.`name`,''),IFNULL(u2.`name`,''),(CASE g.status WHEN 0 THEN '销售' WHEN 1 THEN '租用' ELSE '' END),IFNULL(g.deposit,''),IFNULL(g.agreementNumber,'')) LIKE CONCAT('%',#{word},'%')";

    @Select(SELECT + "AND grantId=#{id}")
    BarcodeMachineGrantVO getByGrantId(@Param("id") int id);

    @Select(SELECT)
    List<BarcodeMachineGrantVO> listAll();

    @Select(SELECT)
    Page<BarcodeMachineGrantVO> pageAll(Page<BarcodeMachineGrantVO> page);

    @Select(SELECT + "AND barcodeMachineNumber=#{id}")
    List<BarcodeMachineGrantVO> listByBarcodeMachineId(@Param("id") int id);

    @Select(SELECT + "AND barcodeMachineNumber=#{id}")
    Page<BarcodeMachineGrantVO> pageByBarcodeMachineId(@Param("id") int id, Page<BarcodeMachineGrantVO> page);

    @Select(SELECT + "AND" + LIKE)
    List<BarcodeMachineGrantVO> listLike(@Param("word") String s);

    @Select(SELECT + "AND" + LIKE)
    Page<BarcodeMachineGrantVO> pageLike(@Param("word") String s, Page<BarcodeMachineGrantVO> page);

    @Select("SELECT * FROM barcode_machine_grant g WHERE (g.status!='2' OR ISNULL(g.`status`)=1) AND g.barcodeMachineNumber = #{barcodeMachineNumber} AND g.isDelete = 0 limit 1")
    BarcodeMachineGrant findByBarcodeMachineNumber(String barcodeMachineNumber);

    @Select(SELECT + " AND g.barcodeMachineNumber = #{barcodeMachineNumber} AND g.grantDate LIKE #{s}")
    BarcodeMachineGrant findByMachineNumberAndGrantDate(String barcodeMachineNumber, String s);

    @Select(SELECT + " AND grantId = #{id}")
    BarcodeMachineGrantVO findById(String id);

    @Select(SELECT)
    List<BarcodeMachineGrantVO> findAll();

    @Select(SELECT + " AND CONCAT_WS(',',g.barcodeMachineNumber, s.stallsName, u1.`name`, u2.`name`, u3.`name`, g.grantDate ) like #{s}")
    List<BarcodeMachineGrantVO> findByLike(String s);

    @Select(DISTINCT)
    List<BarcodeMachineGrantVO> findAllDistinct();

    @Select(DISTINCT + " AND CONCAT_WS(',',g.barcodeMachineNumber, s.stallsName, u1.`name`, u2.`name`, u3.`name`, g.grantDate ) like #{s}")
    List<BarcodeMachineGrantVO> findDistinctByLike(String s);
}
